package com.dragee.utils.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/** https://blog.csdn.net/weixin_42653892/article/details/122618138
 * @author tys on 2023/5/6
 */
public class ExcelUtils {
        /**
         *  单个sheet页导出
         */
        public static void writeExcel(HttpServletResponse response,
                                      List<?> data,
                                      String fileName,
                                      String sheetName,
                                      Class<?> clazz) throws Exception {
            EasyExcel.write(getOutputStream(fileName, response), clazz)
                    .excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
                    .registerWriteHandler(getCellStyle()).doWrite(data);
        }

        /**
         *  sheet页导出.设置下拉框
         */
        public static void writeExcel (HttpServletResponse response,
                                       List<?> data,
                                       String fileName,
                                       String sheetName,
                                       Map<Integer, String[]> dropDownMap,
                                       Class<?> clazz) throws Exception {

            EasyExcel.write(getOutputStream(fileName, response), clazz)
                    .excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
                    .registerWriteHandler(new DownHandler(dropDownMap))
                    .registerWriteHandler(getCellStyle()).doWrite(data);
        }
        /**
         *  合并导出
         */
        public static void writeExcelCustomMerge(HttpServletResponse response,
                                                 List<?> data,
                                                 String fileName,
                                                 String sheetName,
                                                 Class<?> clazz) throws Exception {
            EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(getCellStyle())
                    .head(clazz)
                    .registerWriteHandler(new CustomMergeStrategy(clazz))
                    .doWrite(data);
        }

        /**
         *   多个sheet页导出
         */
        public static void writeExcels (HttpServletResponse response,
                                        List<List<?>> data,
                                        String fileName,
                                        Class<?> clazz) throws Exception{
            ExcelWriter excelWriter = null;
            try{
                excelWriter = EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getCellStyle()).build();
                for(int i=0;i<data.size();i++){
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "sheet"+i).build();
                    excelWriter.write(data.get(i), writeSheet);
                }
            } finally {
                if(excelWriter != null){
                    excelWriter.finish();
                }
            }
        }
        /**
         * 模板复制导出
         * @param data 数据结构最外层为需要导出的sheet页个数，里层为多种数据格式的集合，一般为list和map组合成的混合list
         * @param templateFile 模板本地路径
         */
        public static void copyFillTemplate(HttpServletResponse response,
                                            List<List<?>> data,
                                            String fileName,
                                            String templateFile) throws Exception{
            //excel模板
            File file = new File(templateFile);
            ExcelWriter excelWriter = null;
            try (FileInputStream fileInputStream = new FileInputStream(file); ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
                //通过poi复制出需要的sheet个数的模板
                XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
                workbook.setSheetName(0, "sheet1");
                for (int i = 1; i < data.size();i++) {
                    workbook.cloneSheet(0, "sheet"+(i+1));
                }
                workbook.write(bos);
                byte[] bArray = bos.toByteArray();
                InputStream is = new ByteArrayInputStream(bArray);
                //通过easyExcel塞入参数
                excelWriter = EasyExcel.write(getOutputStream(fileName, response)).withTemplate(is).build();
                fillTemplateData(excelWriter,data);
            } finally {
                if(excelWriter != null){
                    excelWriter.finish();
                }
            }
        }

        /**
         * 模板导出
         */
        public static void fillTemplate(HttpServletResponse response,
                                        List<List<?>> data,
                                        String fileName,
                                        String templateFile) throws Exception{
            ExcelWriter excelWriter = null;
            try  {
                excelWriter = EasyExcel.write(getOutputStream(fileName, response)).withTemplate(new File(templateFile) ).build();
                fillTemplateData(excelWriter,data);
            } finally {
                if(excelWriter != null){
                    excelWriter.finish();
                }
            }
        }
        /**
         * 填充模板数据
         * @param data 数据结构最外层为需要导出的sheet页个数，里层为多种数据格式的集合，一般为list和map组合成的混合list
         */
        private static void fillTemplateData (ExcelWriter excelWriter, List<List<?>> data){
            for (int i = 0; i < data.size(); i++) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i).build();
                for(Object o:data.get(i)){
                    excelWriter.fill(o, writeSheet);
                }
            }
        }

        /**
         * 设置请求
         */
        private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
            fileName = URLEncoder.encode(fileName +"."+ ExcelTypeEnum.XLSX, "UTF-8");
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("content-disposition",  "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName);
            return response.getOutputStream();
        }
        /**
         * 设置样式
         */
        private static HorizontalCellStyleStrategy getCellStyle(){
            //表头样式
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置表头居中对齐
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //内容样式
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置内容靠左对齐
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            return new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
        }
}
